#!/bin/bash
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
use online_edu_da;
insert into time_school_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    itcast_school_id,
    itcast_school_name,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='school'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_school_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    itcast_school_id,
    itcast_school_name,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='type_school'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_subject_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    itcast_subject_id,
    itcast_subject_name,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='type_subject'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_school_subject_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='type_school_subject'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_channel_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    origin_channel,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='type_channel'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_tdepart_signup_cnt
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    tdepart_id,
    tdepart_name,
    signup_cnt
    from online_edu_dws.dws_signup_info_table
    where other_type='type_tdepart'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_relationship_rate
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    concat(cast(if(relationship_cnt=0,0,signup_cnt*10.00000000/relationship_cnt) as varchar),'%') as relationship_rate
    from online_edu_dws.dws_signup_info_table
    where other_type='type'
    order by time_type,"year","month","day"
)
select *
from tmp;

insert into time_type_appeal_rate
with tmp as (
    select
    time_type,
    "year",
    "month",
    "day",
    origin_type,
    concat(cast(if(appeal_cnt=0,0,signup_cnt*10.00000000/appeal_cnt) as varchar),'%') as appeal_rate
    from online_edu_dws.dws_signup_info_table
    where other_type='type'
    order by time_type,"year","month","day"
)
select *
from tmp;
"

